package com.housekeeping.housekeeping.advertisingmanagement.dao.impl;/*
 * @author  pyh
 * @date  2021/1/22 0022 下午 9:18
 * @version 1.0
 * @since JDK1.8_241
 */

import com.housekeeping.housekeeping.advertisingmanagement.dao.ProductDao;
import com.housekeeping.housekeeping.advertisingmanagement.entity.Product;
import com.housekeeping.housekeeping.utils.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements ProductDao {
    @Override
    public int insert(Product product) {
        Connection connection = null;
        PreparedStatement ps = null;
        int i =0;
        try {
            connection = DBManager.getConnection();
            String sql="INSERT INTO product_list(id,number,name,price,norms,stock,status,time) VALUES(?,?,?,?,?,?,?,?)";
            ps = connection.prepareStatement(sql);
            ps.setObject(1,product.getId());
            ps.setObject(2,product.getNumber());
            ps.setObject(3,product.getName());
            ps.setObject(4,product.getPrice());
            ps.setObject(5,product.getNorms());
            ps.setObject(6,product.getStock());
            ps.setObject(7,product.getStatus());
            ps.setObject(8,product.getTime());
            i = ps.executeUpdate();
        }catch (Exception e){

        }finally {
            DBManager.close(ps,connection);
        }
        return i;
    }

    @Override
    public Product update(int id) {
        Connection connection = null;
        PreparedStatement ps = null;
        Product product =null;
        try {
            connection = DBManager.getConnection();
            String sql = "select * from product_list WHERE id = ?"+id;
            ps.executeUpdate();
        }catch (Exception e){

        }finally {
            DBManager.close(ps,connection);
        }
        return product;
    }
    //总条数
    @Override
    public int selectCount() throws SQLException {
        Connection connection = DBManager.getConnection();
        String sql = "SELECT COUNT(*)  FROM product_list WHERE status = 0";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        int total = rs.next() ? rs.getInt(1) : 0;
        DBManager.close(connection, ps);
        return total;
    }

    //分页查询
    @Override
    public List<Product> selectByPage(int limit, int size) throws SQLException {
        List<Product> list = new ArrayList<>();
        Connection connection = DBManager.getConnection();
        String sql = "SELECT * FROM product_list WHERE status = 0  LIMIT ?,? ";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, limit);
        ps.setInt(2, size);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            //之前数据发送成功了但是没有显示数据是因为这里只传了id和name,前端要传递全部数据才能膳食出来
            Product product = new Product();
            product.setId(rs.getInt(1));
            product.setNumber(rs.getInt(2));
            product.setName(rs.getString(3));
            product.setPrice(rs.getDouble(4));
            product.setNorms(rs.getString(5));
            product.setStock(rs.getInt(6));
            product.setStatus(rs.getInt(7));
            product.setTime(rs.getDate(8));
            list.add(product);
        }
        DBManager.close(rs, connection, ps);
        return list;
    }

    @Override
    public int delete(int id) {
        Connection connection = null;
        PreparedStatement ps = null;
        int i = 0;
        try {
            connection = DBManager.getConnection();
            String sql = "update product_list set status = 0 WHERE id = ?";
            ps = connection.prepareStatement(sql);
            ps.setObject(1,(int)id);
            i = ps.executeUpdate();
        }catch (Exception e){

        }finally {
            DBManager.close(ps,connection);
        }
        return i;
    }
}
